﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;

namespace Cashier
{
    class DatabaseServices : IDisposable
    {
        public static AccessDB accessdb = new AccessDB();
        public static MySQLDB mysqldb = new MySQLDB();
        public static AccessDB accessdb2 = new AccessDB();
        /// <summary>
        /// Login service
        /// </summary>
        /// <param name="username">username to login</param>
        /// <param name="password">password for login</param>
        /// <returns>If login successed then return True; else return False</returns>
        public bool Login(string username, string password)
        {         
            DataSet ds = accessdb.ExeQuery(string.Format("select * from employee where username='{0}'", username));
            if (ds.Tables[0].Rows.Count > 0)
            {
                GlobalVar.CurrentLogin = new CurrentEmployee()
                {
                    Id = (int)ds.Tables[0].Rows[0]["id"],
                    EmployeeName = ds.Tables[0].Rows[0]["name"].ToString()
                };
                return true;
            }
            return false;
        }

        /// <summary>
        /// Fill data to the data grid view control
        /// </summary>
        /// <param name="query">Query Statement</param>
        /// <param name="gridView">Data grid view control</param>
        //public void FillDataToGrid(string query, DataGridView gridView)
        //{
        //    accessdb.ExSQLtoDGV(query, gridView);
        //}

        /// <summary>
        /// Get the list employees name by employee type
        /// </summary>
        /// <param name="_employeeType"></param>
        /// <returns></returns>
        public List<string> GetListEmplogyeeNamesByTypeServer(string _employeeType)
        {
            List<string> result = new List<string>();
            string query = string.Format("Select name from employee, employee_type where employee.employee_type_id = employee_type.id and employee_type.type_name = '{0}'", _employeeType);
            DataTable dt = mysqldb.ExecuteQuery(query);
            foreach (DataRow row in dt.Rows)
            {
                string employeeName = row[0].ToString();
                result.Add(employeeName);
            }
            return result;
        }
        public List<string> GetListEmplogyeeNamesByTypeLocal(string _employeeType)
        {
            List<string> result = new List<string>();
            string query = string.Format("Select name from employee, employee_type where employee.employee_type_id = employee_type.id and employee_type.type_name = '{0}'", _employeeType);
            DataSet ds = accessdb.ExeQuery(query);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                string employeeName = row[0].ToString();
                result.Add(employeeName);
            }
            return result;
        }

        public Dictionary<string,string> GetVIPCodes()
        {
            Dictionary<string,string> listCode =new Dictionary<string,string>();
            DataSet ds = accessdb.ExeQuery("Select card_id,full_name from customer");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                string card_id = row["card_id"].ToString();
                if (!listCode.ContainsKey(card_id))
                {
                    listCode.Add(card_id, row["full_name"].ToString());
                }
                /*else
                {
                    listCode[card_id] = row["full_name"].ToString();
                }*/
            }
            return listCode;
        }

        public Dictionary<string, string> GetPhoneList()
        {
            Dictionary<string, string> phoneList = new Dictionary<string, string>();
            DataSet ds = accessdb.ExeQuery("Select phone,card_id from customer");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                string phone = row["phone"].ToString();
                if (phone != "" && !phoneList.ContainsKey(phone))
                {
                    phoneList.Add(phone, row["card_id"].ToString());
                }
            }
            return phoneList;
        }

        public List<Customer> GetCustomerListByConditionServer(string Condition)
        {
            Customer customer = new Customer();
            return customer.getCustomerServerListByCondition(Condition);
        }

        public List<Customer> GetCustomerListByConditionLocal(string Condition)
        {
            Customer customer = new Customer();
            return customer.getCustomerLocalListByCondition(Condition);
        }

        public Customer getCustomerByCardIDServer(string CardID)
        {
            Customer tempCustomer = new Customer();
            tempCustomer.getCustomerServerByCardID(CardID);
            if (tempCustomer.card_id != null)
            {
                return tempCustomer;
            }
            else
            {
                return null;
            }
        }

        public Customer getCustomerByCardIDLocal(string CardID)
        {
            Customer tempCustomer = new Customer();
            tempCustomer.getCustomerLocalByCardID(CardID);
            if (tempCustomer.card_id != null)
            {
                return tempCustomer;
            }
            else
            {
                return null;
            }
        }

        public static bool checkConnectionDb()
        {
            if (mysqldb.OpenConnection())
            {
                mysqldb.CloseConnection();
                return true;
            }
            return false;
        }

        public void Dispose()
        {
 
        }
       /* public bool PushLog()
        {
            LOG customerlog = new LOG();
            List<LOG> LogList = new List<LOG>();
            LogList = customerlog.GetListNewLogLocal();
            return customerlog.UpdateToLogServer(LogList);
        }

        public bool PullData()
        {
            Customer customerserver = new Customer();
            return customerserver.UpdateCustomerLocalFromServer();
        }

        public bool UpdateServer() 
        {
            LOG log = new LOG();
            return log.UpdateCustomerServer();
        }

        public bool WriteLog(Customer _customer)
        {
            //CustomerLog csLog = new CustomerLog();
            return false;
        }*/
        public static void refreshAccessDb()
        {
            //DataTable dt = mysqldb.ExecuteQuery("Select * From Customer");
            accessdb.ExeNonQuery("Delete * From customer");
            /*foreach(DataRow row in dt.Rows)
            {
                DateTime join_date = DateTime.Parse(dr["join_date"].ToString());
                accessdb.ExeNonQuery(@"INSERT INTO CUSTOMER(card_id, full_name, gender, phone, address, join_date, expire_date, birthday, discount_percent, point,is_vip, revision, action) 
                                    values('" + row[1].ToString() + "','" + row[2].ToString() + "','" + bool.Parse(row[3].ToString()) + "','" + row[4].ToString() + "','" + row[5].ToString()
                                              + "','" + row[6].ToString() + "','" + row[7].ToString() + "','" + int.Parse(row[8].ToString()) + "','" + int.Parse(row[9].ToString()) + "','" + int.Parse(row[10].ToString())
                                              + "','" + int.Parse(row[11].ToString()) + "','" + int.Parse(row[12].ToString()) + ") ");
            }*/
            Customer.UpdateCustomerLocalFromServer();
        }
    }
}
